﻿using CommonLib.Util;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.IO;
using System.Linq;

namespace SqlConvertTool.DBContext;

public abstract class BaseDBContext
{
    private DbConnection? Connection { get; set; }

    public List<string>? Tables { get; set; }

    public string TestConnection(string host, string server, string username, string password)
    {
        Connection = ToConnection(host, server, username, password);
        try
        {
            Connection.Open();
            return string.Empty;
        }
        catch (Exception ex) { return ex.Message; }
        finally { Connection.Close(); }
    }

    protected abstract DbConnection ToConnection(string host, string server, string username, string password);


    public string OutPutCreateScript(string path)
    {
        Connection?.Open();
        //var msg = GetTabls();
        //if (!string.IsNullOrEmpty(msg)) return msg;
        //var msg = GetViews(path);
        //if (!string.IsNullOrEmpty(msg)) return msg;
        var msg = GetStore(path);
        if (!string.IsNullOrEmpty(msg)) return msg;
        Connection?.Close();
        return string.Empty;
    }

    private string GetTabls(string path)
    {
        var dt = new DataTable();
        DbDataAdapter? adapter = null;
        try
        {
            adapter = GetTableAdapter();
            adapter.Fill(dt);
            Tables = dt.AsEnumerable().Select(r => Convert.ToString(r[0])!).ToList();
            foreach (var tbl in Tables!.AsParallel())
            {
                using var cmd = TableSqlCommand(tbl);
                var sql = Convert.ToString(cmd.ExecuteScalar());
                File.WriteAllText($"{path}\\{tbl}.sql", sql);
            }
            return string.Empty;
        }
        catch (Exception ex) { return ex.Message; }
        finally { adapter?.Dispose(); }
    }

    protected abstract DbDataAdapter GetTableAdapter();

    private string GetViews(string path)
    {
        var dt = new DataTable();
        DbDataAdapter? adapter = null;
        try
        {
            adapter = GetViewsAdapter();
            adapter.Fill(dt);
            foreach (DataRow dr in dt.Rows)
            {
                var view = dr[0] as string;
                using var cmd = ViewSqlCommand(view!);
                var sql = Convert.ToString(cmd.ExecuteScalar());
                File.WriteAllText($"{path}\\{view}.sql", sql);
            }
            return string.Empty;
        }
        catch (Exception ex) { return ex.Message; }
        finally { adapter?.Dispose(); }
    }

    protected abstract DbDataAdapter GetViewsAdapter();

    protected abstract DbCommand TableSqlCommand(string tbl);

    protected abstract DbCommand ViewSqlCommand(string tbl);

    public string OutPutCSV(string path)
    {
        try
        {
            var sql = "select * from {0}";
            foreach (var t in Tables!.AsParallel())
            {
                var dt = new DataTable();
                using var adp = TableDataAdapter(string.Format(sql, t));
                adp.Fill(dt);
                var list = new List<string>
                {
                    string.Join(",", dt.Columns.Cast<string>().Select(c => $"\"{c}\""))
                };

                foreach (DataRow row in dt.Rows)
                {
                    var items = row.ItemArray.Cast<string>().ToList();
                    for (int i = 0; i < items.Count; i++)
                    {
                        var item = items[i] ?? "";
                        item = row[i].GetType().Equals(typeof(string)) ? $"\"{item}\"" : item;
                    }
                    list.Add(string.Join(",", items));
                }
                File.WriteAllLines($"{path}\\{t}.csv", list);
            }
            return string.Empty;
        }
        catch (Exception ex) { return ex.Message; }
    }

    protected abstract DbDataAdapter TableDataAdapter(string sql);

    protected abstract bool OutPutStore();

    public string ExportData(string tbl, DataTable dt)
    {
        try
        {
            dt = GetData(tbl);
            return string.Empty;
        }
        catch (Exception ex) { return ex.Message; }
    }

    protected abstract DataTable GetData(string tbl);

    public string ImportData(DataTable dt)
    {
        DbTransaction? transaction = null;
        try
        {
            var cols = dt.Columns.Cast<string>().ToList();
            var sql = $"select {string.Join(",", cols)} from {dt.TableName}";
            var tbl = new DataTable(dt.TableName);
            transaction = GetTransaction();
            using var adp = TableDataAdapter(sql);
            adp.Fill(tbl);
            var cmd = GetCommandBulider();
            cmd.DataAdapter = adp;
            foreach (DataRow dr in dt.Rows)
            {
                tbl.Rows.Add(dr.ItemArray);
            }
            cmd.GetInsertCommand();
            adp.Update(dt);
            transaction.Commit();
            return string.Empty;
        }
        catch (Exception ex)
        {
            transaction?.Rollback();
            return ex.Message;
        }
    }

    protected abstract DbCommandBuilder GetCommandBulider();

    protected abstract DbTransaction GetTransaction();

    protected abstract DbDataAdapter GetTriggerAdapter();

    protected abstract DbCommand TriggerCommand(string trigger);

    protected abstract DbDataAdapter GetTypeAdapter();

    protected abstract DbCommand TypeCommand(string type);

    private string GetType(string path)
    {
        var dt = new DataTable();
        DbDataAdapter? adp = null;
        try
        {
            adp = GetTypeAdapter();
            adp.Fill(dt);
            var types = dt.AsEnumerable().Select(r => Convert.ToString(r[0])).ToList();
            foreach (var type in types)
            {
                using var cmd = TypeCommand(type!);
                var sql = Convert.ToString(cmd.ExecuteScalar());
                File.WriteAllText($"{path}\\{type}.sql", sql);
            }
            return string.Empty;
        }
        catch (Exception ex) { return ex.Message; }
        finally { adp?.Dispose(); }
    }

    private string GetStore(string path)
    {
        var list = StoreList(ConstUtil.DataBase);
        try
        {
            foreach (var item in list)
            {
                File.WriteAllText($"{path}\\{item.Item1}.sql", item.Item2);
            }
            return string.Empty;
        }
        catch (Exception ex) { return ex.Message; }
    }

    protected abstract List<Tuple<string, string>> StoreList(string user);

    private string GetTrigger(string path)
    {
        var dt = new DataTable();
        DbDataAdapter? adp = null;
        try
        {
            adp = GetTriggerAdapter();
            adp.Fill(dt);
            var trigs = dt.AsEnumerable().Select(r => Convert.ToString(r[0])).ToList();
            foreach (var trig in trigs)
            {
                using var cmd = TriggerCommand(trig!);
                var sql = Convert.ToString(cmd.ExecuteScalar());
                File.WriteAllText($"{path}\\{trig}.sql", sql);
            }
            return string.Empty;
        }
        catch (Exception ex) { return ex.Message; }
        finally { adp?.Dispose(); }
    }
}
